

********************************************************************************************************************************************
*This .do file takes the monthly PM25 data and finds the school-year PM2.5 average.  It then takes all public schools and finds the nearest 0.1 degree by 0.1 degree
*cell to it and reports that as the school-year pollution faced by the school.  PM2.5 pollution averages and BW PM2.5 gaps can then be calculated using enrollment weights.
*Note this .do file only does the "total" PM2.5 exposure nd gaps, not those solely caused be electricity generation. For those results, run "Table2_PanelB_2.do"

*Inputs: 
*1. Monthly PM25 data files in the "PM25 Data" directory.  These have allready been turned into STATA .dta format using the shp2dta command.
*2. "Schools Data\schoolsdata.dta": School enrollments and school locations from the NCES for school years 2004-05, 2011-12, and 2018-19.  These data were created using the NCES Table generator available at https://nces.ed.gov/ccd/elsi/tablegenerator.aspx.

*Outputs the following results:
*1. Panel B of Table 2 (`Total Exposure' and `Total Exposure Gap' rows only)
*2. Panel A of Table A.5
********************************************************************************************************************************************

clear all
set more off
cd "C:\Users\gilraine\Dropbox\PM25_test_draft\R&R\ReplicationPackage\Derived Data\Table 2 Data"

*This is done manually one year at a time.  
*Manual process: Change one line to your desired year. Specifically, change:
*1. Change year number on line 25 (see note on line 24)

*Change the year to desired year (2005, 2012, or 2019)
local year=2019
local year_p=`year'-1

*Get the PM25 at the monthly level, clean it a bit and then append together
foreach m of numlist 1 2 3 4 5 9 10 11 12 {
clear all
if `m'>8{
use "PM25 Data/`year'/`year_p'_`m'_coord.dta" 
qui merge 1:1 _ID using "PM25 Data/`year'/`year_p'_`m'_database.dta"
}
else if `m'<8{
use "PM25 Data/`year'/`year'_`m'_coord.dta" 
qui merge 1:1 _ID using "PM25 Data/`year'/`year'_`m'_database.dta"
}
drop _merge pointid
ren grid_code PM25
ren _X longitude
ren _Y latitude
gen year=`year'
gen month=`m'
qui compress
qui save "PM25 Data/`year'/`year'_`m'_temp.dta", replace
}
*Append all the months together
clear all
foreach m of numlist 1 2 3 4 5 9 10 11 12 {
qui append using "PM25 Data/`year'/`year'_`m'_temp.dta"
erase "PM25 Data/`year'/`year'_`m'_temp.dta"
}
qui tab month, su(PM25)

drop _ID year month
*Take school-year average at the 0.1 degree by 0.1 degree cell level
collapse PM25, by(latitude longitude)
gen id=_n
qui compress
qui save "PM25 Data\temp.dta", replace


*Bring in the school data
clear all
use "Schools Data\schoolsdata.dta"
qui keep if year==`year'
gen baseid=_n
*Note: This command takes a fair bit of time. You can take out the "ellipsoid"  option if you want it to run faster (at the expense of some minor precision reduction)
geonear baseid lat lon using "PM25 Data/temp.dta", neighbors(id latitude longitude) genstub(id) ellipsoid
*Drop merges that didn't find nearby pollution measurement (just a few odd schools in Alaska)
drop if km_to_id>1

qui merge m:1 id using "PM25 Data/temp.dta"
erase "PM25 Data/temp.dta"
qui keep if _merge==3
drop _merge

*First: Average PM2.5
qui su PM25 [aw=enroll]
di "PM2.5 Exposure"
di r(mean)

*PM2.5 Black-White Gap:
qui su PM25 [aw=black]
local b=r(mean)
qui su PM25 [aw=white]
local w=r(mean)
di "BW PM2.5 Exposure Gap"
di `b'-`w'

*Results.
*2005. All: 11.115033, BW Gap: 1.0368949
*2012. All: 8.6671967, BW Gap: 0.60212696
*2019. All: 7.1507287, BW Gap: 0.42639072

*****TABLE A.5 Panel A ACROSS GAP****
*Across district black-white gap:
preserve
*Collapse to district level; that will just leave the across gap
collapse (rawsum) enroll black white (mean) PM25 [aw=enroll], by(agencyid)
su PM25 [aw=black]
local b=r(mean)
su PM25 [aw=white]
local w=r(mean)
di "BW Across PM2.5 Exposure Gap"
di `b'-`w'
restore
*Results.
*2005. BW Across Gap: 1.0189662
*2012. BW Across Gap: 0.57964883
*2019. BW Across Gap: 0.40227605






